﻿using System;
using Microsoft.VisualStudio.TestTools.UnitTesting;
using System.Runtime.InteropServices;
using Microsoft.Office.Interop.Excel;
using ExcelAutomationRunner;
using System.Diagnostics;
using System.Linq;
using System.Threading.Tasks;
using System.Collections.Generic;
using System.IO;
using OfficeOpenXml;

namespace UnitTestProject1
{
    [TestClass]
    public class ColumnReorderingTest
    {

        private const string SMALL_INPUT_FILE = @"C:\projects\OSDS\FAS\NoelLevitz.OSDS.DataDomain.Test\ImportFiles\bubba_gap.xlsx";
        private const string LARGE_INPUT_FILE = @"C:\projects\OSDS\FAS\NoelLevitz.OSDS.DataDomain.Test\ImportFiles\BiggyBiggyBiggy.xlsx";

        [DllImport( "user32.dll", SetLastError = true )]
        public static extern uint GetWindowThreadProcessId( IntPtr hwnd, out uint lpdwProcessId );

        private const string THREAD_INPUT_FILE = @"C:\projects\OSDS\FAS\NoelLevitz.OSDS.DataDomain.Test\ImportFiles\bubba.xlsx";

        [TestMethod]
        public void ExcelAutomationKill()
        {
            var processIds = GetExcelProcessIds();
            Assert.AreEqual( 1, processIds.Count, "Expected: One copy excel open" );  /// must have excel open
            var excel = new Application { Visible = false, Interactive = false, DisplayAlerts = false, AlertBeforeOverwriting = false };
            var processId = ColumnReorderer.ProcessId( excel );
            excel.Quit();
            var excelProcess = Process.GetProcessById( (int)( processId ) );
            if ( ( excelProcess != null ) && !excelProcess.HasExited )
            {
                Debug.Print( "About to kill excel" );
                excelProcess.Kill();
            }
            //processIds = GetExcelProcessIds();  // can't check here; will still show as process (kill isn't finished).
            //Assert.AreEqual( 1, processIds.Count );
        }

        /// <summary>
        /// Only works when run in debug mode; timing issue.
        /// </summary>
        [TestMethod]
        public void ExcelAutomationMultiThread()
        {
            var tasks = Enumerable.Range( 1, 2 ).ToList().ConvertAll( i => Task.Factory.StartNew( () =>
            {
                Tuple<bool, string> result;
                if ( i == 1 )
                {
                    result = new ColumnReorderer().Reorder( SMALL_INPUT_FILE, new List<string> { "that", "this", "tother" } );
                    return new Tuple<bool, string, string>( result.Item1, SMALL_INPUT_FILE, result.Item2 );
                }
                else
                {
                    result = new ColumnReorderer().Reorder( THREAD_INPUT_FILE, new List<string> { "that", "this" } );
                    return new Tuple<bool, string, string>( result.Item1, THREAD_INPUT_FILE, result.Item2 );
                }
            } ) ).ToArray();
            Task.WaitAll( tasks );
            tasks.ToList().ForEach( t =>
            {
                if ( t.Result.Item2 == SMALL_INPUT_FILE )
                {
                    ValidateSmallFileContents( new Tuple<bool, string>( t.Result.Item1, t.Result.Item3 ), s => CheckSmallFileContents( s ) );
                }
                else
                {
                    ValidateSmallFileContents( new Tuple<bool, string>( t.Result.Item1, t.Result.Item3 ), s => CheckThreadSmallFileContents( s ) );
                }
            } );
        }

        [TestMethod]
        public void MultiThreadSpike()
        {
            var reorderList1 = new List<string> { "personid", "inst_code", "entry_stat", "entry_term", "studentid", "ss_number", "m_name", "saddress1", "saddress2", "scity", "state", "szip_code", "scounty", "l_name", "f_name", "shomephone", "birth_dt", "gender", "date_end", "flag_pro", "date_pro", "flag_inq", "date_inq", "flag_app", "date_app", "app_type", "flag_adm", "date_adm", "flag_con", "date_con", "flag_enr", "flag_canc", "date_canc", "admit_cd", "admit_type", "pers_term", "pers_year", "isource_cd", "hs_grad_yr", "market_seg", "counselor", "academ_lev", "commuter", "church_cd", "denom_cd", "ethnic_cd", "fa_app", "intent", "loading", "m_status", "military", "oren_sess", "priority", "st_int_lvl", "site_cd", "subinst_cd", "trad", "major1", "dept1", "major2", "dept2", "concentration", "attribute", "grdegree", "interest1", "interest2", "othercoll1", "othercoll2", "hscode", "hssource", "standing", "hsgpa", "hsgpascale", "hsgpa_recalc", "clcode", "clsource", "cldegree", "actsource", "actenglish", "actmath", "actreading", "actscience", "actwritten", "act_comp", "act_choice", "satsource", "satverbal", "satmath", "satwritten", "sat_comp", "p_gpa", "c_gpa", "c_gpa_recalc", "hours", "gre", "gmat", "lsat", "mcat", "dat", "oth_test1", "oth_test2", "inst_rat", "early_adm", "cont_app", "cont_adm", "cont_te", "cont_si", "cont_ci", "cont_ti", "cont_ai" };
            var reorderList2 = new List<string> { "cont_cuml", "op_house", "ca_visit", "userdef01", "userdef02", "email", "class", "budget", "userdef03", "userdef04", "userdef05", "userdef06", "userdef07", "userdef08", "userdef09", "userdef10", "tuition", "room", "board", "fees", "other_chgs", "instate", "dep_ind", "fafsa_rcvd", "est_efc", "reported_efc", "other_efc", "pti", "sti", "early_aid", "offer", "empl_tuit", "tuit_exch", "awd_react", "pell_grant", "state_grnt", "awd_cd1", "awd_amt1", "awd_cd2", "awd_amt2", "awd_cd3", "awd_amt3", "awd_cd4", "awd_amt4", "awd_cd5", "awd_amt5", "awd_cd6", "awd_amt6", "awd_cd7", "awd_amt7", "awd_cd8", "awd_amt8", "awd_cd9", "awd_amt9", "awd_cd10", "awd_amt10", "awd_cd11", "awd_amt11", "awd_cd12", "awd_amt12", "awd_cd13", "awd_amt13", "awd_cd14", "awd_amt14", "awd_cd15", "awd_amt15", "awd_cd16", "awd_amt16", "awd_cd17", "awd_amt17", "awd_cd18", "awd_amt18", "awd_cd19", "awd_amt19", "awd_cd20", "awd_amt20", "user_fld1", "user_fld2", "user_fld3", "user_fld4", "user_fld5", "act_first", "sat_first", "enr_coll", "e_decision", "stipend", "cell_no", "last_calc", "tot_fc", "need", "need_met", "gap", "need_wgift", "icg_awd", "i_ug", "i_rg", "i_fg", "im_g", "in_g", "io_g", "ip_g", "it_g", "i__g", "i__w", "i__l", "premier", "talent", "empb", "exchange", "need_aid", "merit_need", "outside", "out_gift", "out_loan", "out_work", "private", "priv_gift", "priv_loan", "priv_work", "cb_gov", "cb_gift", "cb_loan", "cb_work", "fed_gov", "fed_gift", "fed_loan", "state_gov", "state_gift", "excl", "gift", "work", "loan", "academic_tier", "modscore", "distance", "net_tuit", "net_chgs", "est_inc", "subpop", "cluster", "pxgroup", "inc_lvl", "calc_indx", "fs_cum_gpa", "ff_cum_gpa" };

            var tasks = Enumerable.Range( 1, 2 ).ToList().ConvertAll( i => Task.Factory.StartNew( () =>
            {
                Tuple<bool, string> result;
                if ( i == 1 )
                {
                    result = new ColumnReorderer().Reorder( @"C:\temp\fafsa\Source1\Source1.xlsx", reorderList1 );
                    Debug.Write( result.Item2 );
                    return new Tuple<bool, string, string>( result.Item1, SMALL_INPUT_FILE, result.Item2 );
                }
                else
                {
                    result = new ColumnReorderer().Reorder( @"C:\temp\fafsa\Source2\Source2.xlsx", reorderList2 );
                    Debug.Write( result.Item2 );
                    return new Tuple<bool, string, string>( result.Item1, THREAD_INPUT_FILE, result.Item2 );
                }
            } ) ).ToArray();
            Task.WaitAll( tasks );
        }

        [TestMethod]
        public void Temp_yo()
        {
            var reorderList2 = new List<string> { "cont_cuml", "op_house", "ca_visit", "userdef01", "userdef02", "email", "class", "budget", "userdef03", "userdef04", "userdef05", "userdef06", "userdef07", "userdef08", "userdef09", "userdef10", "tuition", "room", "board", "fees", "other_chgs", "instate", "dep_ind", "fafsa_rcvd", "est_efc", "reported_efc", "other_efc", "pti", "sti", "early_aid", "offer", "empl_tuit", "tuit_exch", "awd_react", "pell_grant", "state_grnt", "awd_cd1", "awd_amt1", "awd_cd2", "awd_amt2", "awd_cd3", "awd_amt3", "awd_cd4", "awd_amt4", "awd_cd5", "awd_amt5", "awd_cd6", "awd_amt6", "awd_cd7", "awd_amt7", "awd_cd8", "awd_amt8", "awd_cd9", "awd_amt9", "awd_cd10", "awd_amt10", "awd_cd11", "awd_amt11", "awd_cd12", "awd_amt12", "awd_cd13", "awd_amt13", "awd_cd14", "awd_amt14", "awd_cd15", "awd_amt15", "awd_cd16", "awd_amt16", "awd_cd17", "awd_amt17", "awd_cd18", "awd_amt18", "awd_cd19", "awd_amt19", "awd_cd20", "awd_amt20", "user_fld1", "user_fld2", "user_fld3", "user_fld4", "user_fld5", "act_first", "sat_first", "enr_coll", "e_decision", "stipend", "cell_no", "last_calc", "tot_fc", "need", "need_met", "gap", "need_wgift", "icg_awd", "i_ug", "i_rg", "i_fg", "im_g", "in_g", "io_g", "ip_g", "it_g", "i__g", "i__w", "i__l", "premier", "talent", "empb", "exchange", "need_aid", "merit_need", "outside", "out_gift", "out_loan", "out_work", "private", "priv_gift", "priv_loan", "priv_work", "cb_gov", "cb_gift", "cb_loan", "cb_work", "fed_gov", "fed_gift", "fed_loan", "state_gov", "state_gift", "excl", "gift", "work", "loan", "academic_tier", "modscore", "distance", "net_tuit", "net_chgs", "est_inc", "subpop", "cluster", "pxgroup", "inc_lvl", "calc_indx", "fs_cum_gpa", "ff_cum_gpa" };
            var result = new ColumnReorderer().Reorder( @"C:\temp\fafsa\Source2\Source2.xlsx", reorderList2 );
            Assert.IsTrue( result.Item1, result.Item2 );
        }

        [TestMethod]
        public void ExcelShouldReturnOriginalFileIfColumnsAlreadyMatch()
        {
            var result = new ColumnReorderer().Reorder( SMALL_INPUT_FILE, new List<string> { "this", "that", "tother" } );
            Assert.AreEqual( SMALL_INPUT_FILE, result.Item2 );
        }

        [TestMethod]
        public void ExcelAutomationSmallFile()
        {
            var result = new ColumnReorderer().Reorder( SMALL_INPUT_FILE, new List<string> { "that", "this", "tother" } );
            //Thread.Sleep( 500 );  // Can't access the file for short time... ?
            ValidateSmallFileContents( result, s => CheckSmallFileContents( s ) );
        }

        [TestMethod]
        public void ExcelAutomationLargeFile()
        {
            var ticky = Environment.TickCount;
            var result = new ColumnReorderer().Reorder( LARGE_INPUT_FILE, new List<string> { "ENTRY_TERM", "STUDENTID", "M_NAME", "ENTRY_STAT", "L_NAME", "F_NAME", "SADDRESS1", "SADDRESS2", "SCITY", "STATE", "SZIP_CODE", "FLAG_ADM", "DATE_ADM", "FLAG_CON", "DATE_CON", "FLAG_CANC", "DATE_CANC", "FLAG_ENR", "DATE_END", "LOADING", "GENDER", "COMMUTER", "ETHNIC_CD", "MAJOR1", "MAJOR2", "DEPT1", "DEPT2", "CLASS", "INTENT", "STANDING", "HSGPA", "HSGPASCALE", "HSGPA_RECALC", "ACT_COMP", "SAT_COMP", "P_GPA", "C_GPA", "USERDEF04", "BUDGET", "TUITION", "ROOM", "BOARD", "FEES", "OTHER_CHGS", "FAFSA_RCVD", "EST_EFC", "DEP_IND", "REPORTED_EFC", "PTI", "STI", "OFFER", "EMPL_TUIT", "TUIT_EXCH", "AWD_CD1", "AWD_AMT1", "AWD_CD2", "AWD_AMT2", "AWD_CD3", "AWD_AMT3", "AWD_CD4", "AWD_AMT4", "AWD_CD5", "AWD_AMT5", "AWD_CD6", "AWD_AMT6", "AWD_CD7", "AWD_AMT7", "AWD_CD8", "AWD_AMT8", "AWD_CD9", "AWD_AMT9", "AWD_CD10", "AWD_AMT10", "AWD_CD11", "AWD_AMT11", "AWD_CD12", "AWD_AMT12", "AWD_CD13", "AWD_AMT13", "AWD_CD14", "AWD_AMT14", "AWD_CD15", "AWD_AMT15", "AWD_CD16", "AWD_AMT16", "AWD_CD17", "AWD_AMT17", "AWD_CD18", "AWD_AMT18", "AWD_CD19", "AWD_AMT19", "AWD_CD20", "AWD_AMT20", "PERS_TERM", "PERS_YEAR", "C_GPA_RECALC", "FLAG_APP", "DATE_APP", "APP_TYPE", "EARLY_ADM", "USERDEF01", "ADMIT_TYPE", "USERDEF02", "CHURCH_CD", "DENOM_CD", "HSCODE", "INTEREST1", "INTEREST2", "SITE_CD", "SUBINST_CD", "OP_HOUSE", "CA_VISIT", "ACTENGLISH", "ACTMATH", "ACTREADING", "ACTSCIENCE", "ACTWRITTEN", "SATVERBAL", "SATMATH", "SATWRITTEN" } );
            /// 15-16 minutes (save after each column)
            /// Only one save 8-9 minutes
            ValidateLargeFile( result, ticky, nameof(ExcelAutomationLargeFile) );
        }

        private static List<int> GetExcelProcessIds()
        {
            return Process.GetProcesses().Where( p => p.ProcessName.Contains( "EXCEL" ) ).Select( p => p.Id ).ToList();
        }

        /// <summary>
        /// ORIGINAL Contents at time of writing:
        /// this      | that    
        /// like this | like that
        /// a like    | that unh
        /// </summary>
        /// <param name="newFilePath"></param>
        private static void CheckThreadSmallFileContents( string newFilePath )
        {
            using ( var p = new ExcelPackage( new FileInfo( newFilePath ) ) )
            {
                Assert.IsTrue( p.Workbook.Worksheets.Count == 1 );
                var worksheet = p.Workbook.Worksheets.First();
                Assert.AreEqual( "that", worksheet.Cells[1, 1].Value );
                Assert.AreEqual( "like that", worksheet.Cells[2, 1].Value );
                Assert.AreEqual( "that unh", worksheet.Cells[3, 1].Value );
                Assert.AreEqual( "this", worksheet.Cells[1, 2].Value );
                Assert.AreEqual( "like this", worksheet.Cells[2, 2].Value );
                Assert.AreEqual( "a like", worksheet.Cells[3, 2].Value );
            }
        }

        private static void ValidateSmallFileContents( Tuple<bool, string> result, Action<string> a )
        {
            Assert.IsTrue( result.Item1, result.Item2 );
            Assert.IsTrue( File.Exists( result.Item2 ) );
            a( result.Item2 );
            File.Delete( result.Item2 );
        }

        /// <summary>
        /// ORIGINAL Contents at time of writing:
        /// this      | that    | tother
        /// like this |         | what
        /// a like    | that unh| ever
        /// </summary>
        /// <param name="newFilePath"></param>
        private static void CheckSmallFileContents( string newFilePath )
        {
            using ( var p = new ExcelPackage( new FileInfo( newFilePath ) ) )
            {
                Assert.IsTrue( p.Workbook.Worksheets.Count == 1 );
                var worksheet = p.Workbook.Worksheets.First();
                Assert.AreEqual( "that", worksheet.Cells[1, 1].Value );
                Assert.AreEqual( "that unh", worksheet.Cells[3, 1].Value );
                Assert.AreEqual( "this", worksheet.Cells[1, 2].Value );
                Assert.AreEqual( "like this", worksheet.Cells[2, 2].Value );
                Assert.AreEqual( "a like", worksheet.Cells[3, 2].Value );
                Assert.AreEqual( "tother", worksheet.Cells[1, 3].Value );
                Assert.AreEqual( "what", worksheet.Cells[2, 3].Value );
                Assert.AreEqual( "ever", worksheet.Cells[3, 3].Value );
            }
        }

        private static void CheckLargeFileContents( string newFilePath )
        {
            using ( var p = new ExcelPackage( new FileInfo( newFilePath ) ) )
            {
                Assert.IsTrue( p.Workbook.Worksheets.Count == 1 );
                var worksheet = p.Workbook.Worksheets.First();
                Assert.AreEqual( "ENTRY_TERM", worksheet.Cells[1, 1].Value );  // top left col name
                Assert.AreEqual( "000001696", worksheet.Cells[2, 2].Value );  // first val in second col: student id
                Assert.AreEqual( "L", worksheet.Cells[2, 3].Value );  // first val in third col: m_name
            }
        }

        private static void ValidateLargeFile( Tuple<bool, string> result, int preTickCount, string whoCalledMe )
        {
            Debug.Print( "Minutes taken for {0}: {1}", whoCalledMe, ( ( Environment.TickCount - preTickCount ) / 1000 ) / 60 );
            Assert.IsTrue( result.Item1, result.Item2 );
            Assert.IsTrue( File.Exists( result.Item2 ) );
            CheckLargeFileContents( result.Item2 );
            File.Delete( result.Item2 );
        }

    }
}
